package com.example.service;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.alibaba.fastjson2.filter.SimplePropertyPreFilter;
import com.example.dao.SlowSqlInfo;
import com.example.dao.SlowSqlInfoRepository;
import com.example.util.LogUtil;
import com.example.util.ShellExecutor;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Stream;

import static com.example.util.ShellExecutor.executeCommand;

@Service
public class SlowSqlInfoService {
    private final SlowSqlInfoRepository slowSqlInfoRepository;
    private final SlowSqlChecker slowSqlChecker;

    public SlowSqlInfoService(SlowSqlInfoRepository slowSqlInfoRepository, SlowSqlChecker slowSqlChecker) {
        this.slowSqlInfoRepository = slowSqlInfoRepository;
        this.slowSqlChecker = slowSqlChecker;
    }

    @Scheduled(cron = "${custom-config.scheduled.ptRunCron}")
    public void runPtQueryDigest() {
        List<String> command = new ArrayList<>();
        command.add("/usr/bin/ansible-playbook");
        command.add("-i");
        command.add("/home/paas/lingyang/java_projects/slowlog-analysis/ansible/pas_hosts");
        command.add("/home/paas/lingyang/java_projects/slowlog-analysis/ansible/pt-analyze-sql.yml");
//        command.add("cmd");
//        command.add("/c");
//        command.add("dir");
        try {
            ShellExecutor.ExecuteResult result = executeCommand(
                    command,
                    180,
                    new File("."),
                    Map.of("MY_ENV", "test_value"),
                    Charset.defaultCharset()
            );
            LogUtil.logInfo("执行命令：" + command.toString());
            LogUtil.logDebug("Exit Code: " + result.exitCode());
            LogUtil.logDebug("Output:\n" + result.output());
            LogUtil.logInfo("Error:\n" + result.error());
            analyzePtJsonFile();
        } catch (Exception e) {
            LogUtil.logError(e.toString(), e);
        }
    }

    public void analyzePtJsonFile() {
        String rootPath = System.getProperty("user.dir");
        Path ptJsonPath = Paths.get(rootPath, "files");
        Path bakJsonDir = Paths.get(rootPath, "bak");

        if (!Files.isDirectory(ptJsonPath)) {
            return;
        }
        try (Stream<Path> paths = Files.walk(ptJsonPath)) {
            List<Path> jsonFiles = paths
                    .filter(Files::isRegularFile)  // 确保是文件
                    .filter(path -> path.toString().endsWith(".json"))
                    .toList();
            if (jsonFiles.isEmpty()) {
                return;
            }
            for (Path file : jsonFiles) {
                try {
                    String content = Files.readString(file);
                    if (content.isEmpty()) {
                        Path bakJsonPath = bakJsonDir.resolve(file.getFileName().toString());
                        Files.move(file, bakJsonPath, StandardCopyOption.REPLACE_EXISTING);
                        continue;
                    }
                    parsePtJson(content, file.toString());
                    Path bakJsonPath = bakJsonDir.resolve(file.getFileName().toString());
                    Files.move(file, bakJsonPath, StandardCopyOption.REPLACE_EXISTING);
                } catch (IOException e) {
                    LogUtil.logError(e.toString(), e);
                }
            }
        } catch (IOException e) {
            LogUtil.logError("目录遍历失败", e);
        }
    }

    public void parsePtJson(String ptJson, String jsonFilePath) {
//        System.out.println("ptJson: " + ptJson);
        JSONObject rootObject = JSON.parseObject(ptJson);
//        System.out.println("rootObject: " + JSON.toJSONString(rootObject));
        try {
            JSONArray classesList = rootObject.getJSONArray("classes");
            JSONObject globalObject = rootObject.getJSONObject("global");
            for (Object object : classesList) {
                JSONObject classObject = (JSONObject) object;
                SlowSqlInfo slowSqlInfo = new SlowSqlInfo();
                slowSqlInfo.setChecksum(classObject.getString("checksum"));
                slowSqlInfo.setDistillate(classObject.getString("distillate"));
                slowSqlInfo.setFingerprint(classObject.getString("fingerprint"));
                slowSqlInfo.setQueryCount(classObject.getString("query_count"));
                slowSqlInfo.setTsMax(classObject.getString("ts_max"));
                slowSqlInfo.setTsMin(classObject.getString("ts_min"));
                slowSqlInfo.setDb(classObject.getJSONObject("metrics").getJSONObject("db").getString("value"));
                slowSqlInfo.setUsername(classObject.getJSONObject("metrics").getJSONObject("user").getString("value"));
                String queryTimeSum = classObject.getJSONObject("metrics").getJSONObject("Query_time").getString("sum");
                slowSqlInfo.setQueryTimeSum(queryTimeSum);
                slowSqlInfo.setQueryTimeAvg(classObject.getJSONObject("metrics").getJSONObject("Query_time").getString("avg"));
                String globalQueryTimeSum = globalObject.getJSONObject("metrics").getJSONObject("Query_time").getString("sum");
                String QueryTimePctInGlobal = String.format("%.2f", (Double.parseDouble(queryTimeSum)/ Double.parseDouble(globalQueryTimeSum)) * 100) + "%";
                slowSqlInfo.setQueryTimePctInGlobal(QueryTimePctInGlobal);
                JSONArray tempList = globalObject.getJSONArray("files");
                String name = tempList.getJSONObject(0).getString("name");
                String size = tempList.getJSONObject(0).getString("size");
                String [] parts = name.split("/");
                String ipPortStr = parts[parts.length - 2];
                String [] ipPort = ipPortStr.split("_");
                String host = ipPort[0];
                String port = ipPort[1];
                String clientHost = classObject.getJSONObject("metrics").getJSONObject("host").getString("value");
                slowSqlInfo.setHost(host);
                slowSqlInfo.setPort(port);
                slowSqlInfo.setClientHost(clientHost);
                slowSqlInfo.setRawFilePath(name);
                slowSqlInfo.setRawFileSize(size);
                slowSqlInfo.setJsonFilePath(jsonFilePath);
                slowSqlInfo.setUpdateTime(new Timestamp(System.currentTimeMillis()));
                slowSqlInfoRepository.save(slowSqlInfo);
                slowSqlChecker.check(slowSqlInfo);
            }
        } catch (Exception e) {
            LogUtil.logError(e.toString(), e);
            LogUtil.logInfo("解析ptJson出错："+ ptJson);
        }

    }

    public String getSlowSqlInfo(Boolean recent, Boolean jsonFormat) {
//        SimplePropertyPreFilter filter = new SimplePropertyPreFilter(SlowSqlInfo.class, "id", "checksum", "distillate", "jsonFilePath");
        SimplePropertyPreFilter filter = new SimplePropertyPreFilter();
        filter.getExcludes().add("id");
        filter.getExcludes().add("checksum");
        filter.getExcludes().add("distillate");
        filter.getExcludes().add("jsonFilePath");
        List<SlowSqlInfo> slowSqlInfos;
        if (!recent) {
            slowSqlInfos = slowSqlInfoRepository.findAll();
        } else {
            Timestamp updateTimeThreshold = new Timestamp(System.currentTimeMillis() - 30 * 60 * 1000);
//            System.out.println("updateTimeThreshold: " + updateTimeThreshold);
            slowSqlInfos = slowSqlInfoRepository.findByUpdateTime(updateTimeThreshold);
        }

        List<String> tempList = new ArrayList<>();

        for (SlowSqlInfo slowSqlInfo : slowSqlInfos) {
            String slowSqlInfoJson = JSON.toJSONString(slowSqlInfo, filter);
            tempList.add(slowSqlInfoJson);
        }
        if (jsonFormat) {
            return tempList.toString();
        }
        return String.join(System.lineSeparator(), tempList);
    }
}
